﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Utility;
using Utility.Helper;

namespace BLL
{
    public class Sys_CompanyBLL : BaseBLL
    {
        public Sys_CompanyBLL()
        {
        }

        public Sys_CompanyBLL(Service service)
        {
            this.Service = service;
        }

        public DataTable GetAllInfo()
        {
            string sql = string.Format(@"SELECT
    sysc.CMY_ID,
    sysc.CMY_NAME,
    sysc.CMY_NAME2,
    sysc.CMY_LEVEL,
    CASE sysc.CMY_TYPE
    WHEN '-1' THEN '总公司'
    WHEN '0' THEN '分公司'
    WHEN '1' THEN '控股公司'
    END as cmyType,
    sysc.CMY_PARENTID
FROM
    Sys_Company sysc
ORDER BY sysc.CMY_ID");
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable SelectCompanys(string companyId)
        {
            string sql = string.Format(@"select cmy_id, cmy_name, cmy_name2,  cmy_level, cmy_type,
              cmy_parentid from SYS_COMPANY t where t.cmy_id in (" + companyId + ") order by cmy_id ");

            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetAllInfo(string whereStr)
        {
            string sql = string.Format(@"SELECT
    sysc.CMY_ID,
    sysc.CMY_NAME,
    sysc.CMY_NAME2,
    sysc.CMY_LEVEL,
    CASE sysc.CMY_TYPE
    WHEN '-1' THEN '总公司'
    WHEN '0' THEN '分公司'
    WHEN '1' THEN '控股公司'
    END as cmyType,
    sysc.CMY_PARENTID
FROM
    Sys_Company sysc
WHERE 1=1 ");
            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }
            sql += " ORDER BY CMY_ID ASC ";
            DataTable dt = Query(sql);
            return dt;
        }

        public Sys_Company Find(decimal id)
        {
            return GetInfoById(id.ToString().Trim());
        }

        public DataTable GetInfoByParentId(string pid)
        {
            string sql = string.Format(@"SELECT
    sysc.CMY_ID,
    sysc.CMY_NAME,
    sysc.CMY_NAME2,
    sysc.CMY_LEVEL,
    CASE sysc.CMY_TYPE
    WHEN '-1' THEN '总公司'
    WHEN '0' THEN '分公司'
    WHEN '1' THEN '控股公司'
    END as cmyType,
    sysc.CMY_PARENTID
FROM
    Sys_Company sysc
WHERE
    sysc.CMY_PARENTID ='{0}'
ORDER BY sysc.CMY_ID", pid);
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetInfoByPId(int pid)
        {
            var sql = string.Format(@"SELECT CMY_ID,
       CMY_NAME,
       CMY_NAME2,
       CMY_LEVEL,
       CMY_TYPE,
       CMY_PARENTID,
       t.c_code,
       (SELECT T2.CMY_NAME
          FROM SYS_COMPANY T2
         WHERE T2.CMY_ID = t.CMY_PARENTID) AS PARENTNAME
  FROM SYS_COMPANY T START WITH CMY_ID = {0} CONNECT BY PRIOR CMY_ID = CMY_PARENTID", pid);
            var dt = Query(sql);
            /*
            dt.Columns.Add("");
            var alldata = GetAll();
            foreach (var item in collection)
            {
            }
           */
            /*
            var all = GetAll();
            var data = GetChilds(all, decimal.Parse(pid));
            var dt = new DataTable();
            dt.Columns.Add("CMY_ID");
            dt.Columns.Add("CMY_NAME");
            dt.Columns.Add("CMY_NAME2");
            dt.Columns.Add("CMY_LEVEL");
            dt.Columns.Add("CMY_TYPE");
            dt.Columns.Add("CMY_PARENTID");

            foreach (var item in data)
            {
                var row=dt.NewRow();
                row["CMY_ID"] = item.CMY_ID;
                row["CMY_NAME"] = item.CMY_NAME;
                row["CMY_NAME2"] = item.CMY_NAME2;
                row["CMY_LEVEL"] = item.CMY_LEVEL;
                row["CMY_TYPE"] = item.CMY_TYPE;
                row["CMY_PARENTID"] = item.CMY_PARENTID;
                dt.Rows.Add(row);
            }
            */
            return dt;
        }

        private List<Sys_Company> GetChilds(List<Sys_Company> data, decimal pid)
        {
            var datax = data.Where(a => a.CMY_PARENTID == pid).ToList();
            var xdata = new List<Sys_Company>();
            var entity = data.SingleOrDefault(a => a.CMY_ID == pid);
            if (entity != null)
            {
                xdata.Add(entity);
            }
            foreach (var item in datax)
            {
                xdata.AddRange(GetChilds(data, item.CMY_ID));
            }
            datax.AddRange(xdata);
            return datax;
        }

        /// <summary>
        /// 通过公司全称获取公司信息
        /// </summary>
        /// <param name="name">公司名称</param>
        /// <returns>公司信息</returns>
        public Sys_Company GetInfoByName(string name)
        {
            var sql = @"SELECT * FROM SYS_COMPANY T1
                        WHERE
                            T1.CMY_NAME = :NAME";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":NAME", name);
            var dt = Query(sql, parms);
            Sys_Company company = null;
            if (dt.Rows.Count == 1)
            {
                company = ConvertToEntity(dt.Rows[0]);
            }
            return company;
        }

        public List<Sys_Company> GetChilds(decimal pid)
        {
            return GetChilds(GetAll(), pid);
        }

        public string GetChildsByIDStr(decimal pid)
        {
            var data = GetChilds(pid).Distinct().OrderBy(a => a.CMY_ID).ToList();
            var str = "";
            for (int i = 0; i < data.Count; i++)
            {
                str += data[i].CMY_ID.ToString().Trim();
                if (i + 1 != data.Count)
                {
                    str += ",";
                }
            }
            return str;
        }

        /// <summary>
        /// 根据公司编号查询公司信息
        /// </summary>
        /// <param name="NId"></param>
        /// <returns></returns>
        public Sys_Company GetInfoById(string CId)
        {
            //sql语句
            string sql = string.Format(@"SELECT CMY_ID,CMY_NAME,CMY_NAME2,CMY_LEVEL,CMY_TYPE,CMY_PARENTID,C_CODE FROM SYS_COMPANY WHERE CMY_ID={0}", CId);

            //从数据库获取数据
            DataTable dt = Query(sql);

            //实例化一个公司信息实体类
            Sys_Company model = new Sys_Company();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.CMY_ID = decimal.Parse(dt.Rows[0]["CMY_ID"].ToString());
                model.CMY_NAME = dt.Rows[0]["CMY_NAME"].ToString();
                model.CMY_NAME2 = dt.Rows[0]["CMY_NAME2"].ToString();
                model.CMY_LEVEL = decimal.Parse(dt.Rows[0]["CMY_LEVEL"].ToString());
                model.CMY_TYPE = dt.Rows[0]["CMY_TYPE"].ToString();
                model.CMY_PARENTID = decimal.Parse(dt.Rows[0]["CMY_PARENTID"].ToString());

                model.C_CODE = dt.Rows[0]["C_CODE"].ToString();
            }
            return model;
        }

        public List<Sys_Company> GetAll()
        {
            var obj = HttpRuntimeCache.Get("Sys_Company");
            List<Sys_Company> data;
            if (obj == null)
            {
                var sql = "SELECT CMY_ID,CMY_NAME,CMY_NAME2,CMY_LEVEL,CMY_TYPE,CMY_PARENTID FROM SYS_COMPANY";
                var dt = Query(sql);
                data = new List<Sys_Company>();
                foreach (DataRow item in dt.Rows)
                {
                    var entity = new Sys_Company
                    {
                        CMY_ID = decimal.Parse(item["CMY_ID"].ToString()),
                        CMY_NAME = item["CMY_NAME"].ToString(),
                        CMY_NAME2 = item["CMY_NAME2"] == DBNull.Value ? "" : item["CMY_NAME2"].ToString(),
                        CMY_LEVEL = decimal.Parse(item["CMY_LEVEL"].ToString()),
                        CMY_TYPE = item["CMY_TYPE"] == DBNull.Value ? "" : item["CMY_TYPE"].ToString(),
                        CMY_PARENTID = item["CMY_PARENTID"] == DBNull.Value ? 0 : decimal.Parse(item["CMY_PARENTID"].ToString())
                    };
                    data.Add(entity);
                }
                HttpRuntimeCache.Set("Sys_Company", data, 60, false);
            }
            else
            {
                data = (List<Sys_Company>)obj;
            }
            return data.ToList();
            /*
            var sql = "SELECT CMY_ID,CMY_NAME,CMY_NAME2,CMY_LEVEL,CMY_TYPE,CMY_PARENTID FROM SYS_COMPANY";
            DataTable dt = DbHelperSQL.GetDataTable(sql);
            var data = new List<Sys_Company>();
            foreach (DataRow item in dt.Rows)
            {
                var enrity = new Sys_Company();
                enrity.CMY_ID = decimal.Parse(item["CMY_ID"].ToString());
                enrity.CMY_NAME = item["CMY_NAME"].ToString();
                enrity.CMY_NAME2 = item["CMY_NAME2"].ToString();
                enrity.CMY_LEVEL = decimal.Parse(item["CMY_LEVEL"].ToString());
                enrity.CMY_TYPE = item["CMY_TYPE"].ToString();
                enrity.CMY_PARENTID = decimal.Parse(item["CMY_PARENTID"].ToString());
                data.Add(enrity);
            }
            return data;
            */
        }

        public int INSERT(Sys_Company model, string operName)
        {
            model.CMY_ID = GetSequence("CMY_ID");
            try
            {
                var sql = string.Format(@"INSERT INTO SYS_COMPANY(
                                            CMY_ID,
                                            CMY_NAME,
                                            CMY_NAME2,
                                            CMY_LEVEL,
                                            CMY_TYPE,
                                            CMY_PARENTID,C_CODE)
                                            VALUES({5},'{0}', '{1}', {2}, '{3}', {4},'{6}')
", model.CMY_NAME, model.CMY_NAME2, model.CMY_LEVEL, model.CMY_TYPE, model.CMY_PARENTID, model.CMY_ID, model.C_CODE);
                int count = NoQuery(sql, operName);
                HttpRuntimeCache.Remove("Sys_Company");
                return count;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public int UPDATE(Sys_Company model, string operName)
        {
            try
            {
                string sql = string.Format(@"UPDATE SYS_COMPANY SET
CMY_NAME = '{0}',
CMY_NAME2 = '{1}',
CMY_LEVEL = {2},
CMY_TYPE = '{3}',
C_CODE='{6}',
CMY_PARENTID = {4}
WHERE CMY_ID = {5}
", model.CMY_NAME, model.CMY_NAME2, model.CMY_LEVEL, model.CMY_TYPE, model.CMY_PARENTID, model.CMY_ID, model.C_CODE);
                int count = NoQuery(sql, operName);
                HttpRuntimeCache.Remove("Sys_Company");
                return count;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public int DELETE(string ids, string operName)
        {
            try
            {
                string sql = string.Format(@"DELETE FROM THKQS.Sys_Company WHERE CMY_ID IN(" + ids + ")");
                int count = NoQuery(sql, operName);
                return count;
            }
            finally
            {
                HttpRuntimeCache.Remove("Sys_Company");
            }
        }

        public DataTable SelectByWhere(string whereStr, string comId)
        {
            string sql = string.Format(@"SELECT CMY_ID,
       CMY_NAME,
       CMY_NAME2,
       CMY_LEVEL,
       decode(CMY_TYPE, 0, '分公司', 1, '控股公司', -1, '总公司') CMY_TYPE,
       CMY_PARENTID,
       T2.C_CODE,
       (SELECT CMY_NAME
          FROM SYS_COMPANY T1
         WHERE T1.CMY_ID = T2.CMY_PARENTID) AS parentName
  FROM SYS_COMPANY T2
 where t2.cmy_id in
       (select d.cmy_id
          from SYS_COMPANY d
         start with d.cmy_id = {0}
        connect by prior d.cmy_id = d.CMY_PARENTID)
" + whereStr, comId);

            /*执行Sql*/
            var dt = Query(sql);
            return dt;
            /* sql = string.Format(@"SELECT
    c.CMY_ID,
    c.CMY_NAME,
    c.CMY_NAME2,
    c.CMY_LEVEL,
    c.CMY_PAESONID,
    u.USER_NAME,
    CASE c.CMY_TYPE
    WHEN '-1' THEN
    '总公司'
    WHEN '0' THEN
    '分公司'
    WHEN '1' THEN
    '控股公司'
    END as cmyType,
    c.CMY_PARENTID,
    (SELECT CMY_NAME  FROM THKQS.Sys_Company sc
    WHERE sc.CMY_ID = c.CMY_PARENTID) parentName
FROM
    THKQS.Sys_Company c,
    THKQS.Sys_User u
WHERE exists (SELECT 1
    FROM
         THKQS.Sys_Company c
    WHERE
        c.CMY_ID = c.CMY_ID
        AND c.CMY_ID = " + comId + @"
        OR (c.CMY_ID = c.CMY_PARENTID))
        AND c.CMY_PAESONID=u.USER_ID " + whereStr + " ");
            DataTable dt = DbHelperSQL.GetDataTable(sql);
            return dt;
            */
        }

        public DataTable GetUserCompanyPerson(string depid)
        {
            string sql = string.Format(@"SELECT
	sysu.USER_ID,
	sysu.USER_NAME,
	sysu.USER_LOGINNAME,
	sysu.USER_PWD,
	sysu.USER_ROLEID,
	sysu.USER_JOBSTATUS,
	sysu.USER_SEX,
	sysu.USER_PHONE,
	sysu.USER_LOGINSTATUS,
	sysu.USER_ONLINESTATUS,
	sysu.user_logintime,
	sysu.USER_COMPANY,
	sysu.USER_DEPARTMENT
FROM
	THKQS.Sys_User sysu,
    THKQS.Sys_Department sysd
WHERE
    sysu.USER_DEPARTMENT=sysd.DEPTID ");
            if (!(string.IsNullOrEmpty(depid)))
            {
                sql += " AND (sysu.USER_DEPARTMENT=" + depid + " OR sysd.DEPTPARENTID=" + depid + ")";
            }
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetCompanyFatherAndChild(decimal cmyid)
        {
            var sql = @"SELECT * FROM SYS_COMPANY
START WITH CMY_ID = {0}
CONNECT BY PRIOR CMY_ID = CMY_PARENTID";
            sql = string.Format(sql, cmyid);
            return Query(sql);
        }

        public string GetCompanyFatherAndChildByID(decimal cmyid)
        {
            var sql = @"SELECT
CMY_ID
FROM
(
SELECT * FROM SYS_COMPANY
START WITH CMY_ID = {0}
CONNECT BY PRIOR CMY_ID = CMY_PARENTID)";
            sql = string.Format(sql, cmyid);
            var dt = Query(sql);
            var str = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i + 1 == dt.Rows.Count)
                {
                    str += dt.Rows[i]["CMY_ID"].ToString();
                }
                else
                {
                    str += dt.Rows[i]["CMY_ID"].ToString() + ",";
                }
            }
            return str;
        }

        #region 根据id查询公司

        public List<Sys_Company> GetEntitys(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var dt = GetData(whereStr, orderbyStr, skip, take, out count);
            var entitys = new List<Sys_Company>();
            foreach (DataRow item in dt.Rows)
            {
                entitys.Add(ConvertToEntity(item));
            }
            return entitys;
        }

        public DataTable GetData(string whereStr, string orderbyStr, int skip, int take, out int count)
        {
            var sqlBuilder = new StringBuilder();
            sqlBuilder.AppendLine("SELECT * FROM SYS_COMPANY");
            if (whereStr != null)
            {
                sqlBuilder.AppendLine(" WHERE " + whereStr);
            }
            if (orderbyStr != null)
            {
                sqlBuilder.AppendLine(" ORDER BY " + orderbyStr);
            }
            count = SqlCount(sqlBuilder.ToString());
            return PageQuery(sqlBuilder.ToString(), skip, take);
        }

        private Sys_Company ConvertToEntity(DataRow row)
        {
            if (row == null)
            {
                return null;
            }
            var entity = new Sys_Company
            {
                CMY_ID = Convert.ToDecimal(row["CMY_ID"]),
                CMY_NAME = Convert.ToString(row["CMY_NAME"])
            };
            return entity;
        }

        #endregion 根据id查询公司
    }
}